### RFIDServer.py
### Read a list of valid rfid cardNums from a file
#for using the serial port
import serial
import sys
#import sqlite3
import MySQLdb
import MySQLdb.cursors
import time
import threading
import socket
#for twitter:
import subprocess
#import os
#import pymysql
#constants/definitions:
argFobNumIndex = 2
argDoorNumIndex = 1
argCmdIndex = 0
#door 0 is shop, door 1 is blue room, door 2 is front
doorNames = ['shop','blue room','front hall']

#db info:	

database = "ENTS"
table = "members"
hostaddr = "172.16.15.101"					#use ip addr in case name changes, should be ents-monitoring
username = "mastercontrol"
password = doors_password


firstNameIndex = 0
logFile = "RFIDDoorLog.txt"
#field info
AnnounceField = 'AnnouncePresence'
TwitName = 'TwitterAccnt'
NickName = 'Nickname'
FirstName = 'FirstName'
LastName = 'LastName'
IRCName = 'IRCNick'
FobField = 'FobNumber'
LastUnlockField = "LastAnnounce"
#announce info, timoute in seconds	(30 minutes)
announceTimeoutSecs = 30*60


ser = serial.Serial('/dev/ttyUSB0',115200, timeout=5)		#open USB serial port 0

#the ircBot thread
import irclib
import Queue																		
ircQueue = Queue.Queue() #Queue.Queue()																#irc message que used to pass messages to ircbot thread
#irc bot info
HOST= "irc.freenode.net"
PORT=6667
NICK="MasterControl"
IDENT="BigBrother"
REALNAME="Master Control"
CHAN="#ENTS"
EXIT_MSG="EXIT"

EMAIL_SERVER="localhost"
EMAIL_TO="directors@ents.ca"
EMAIL_SENDER="mastercontrol@ents.ca"
import smtplib
def failMail(subject, message):	#serverURL=None, sender='', to='', subject='', text=''):
	serverURL=EMAIL_SERVER
	sender = EMAIL_SENDER
	to = EMAIL_TO
  
	headers = "From: %s\r\nTo: %s\r\nSubject: %s\r\n\r\n" % (sender, to, subject)
	message = headers + message
	mailServer = smtplib.SMTP(serverURL)
	mailServer.sendmail(sender, to, message)
	mailServer.quit()

failMail("mastercontrol has started","This is a mastercontrol admin email to notify you that Mastercontrol has started")


def getLastUnlock( doorNum ):
#	try:
		print "connecting to db"
		conn = MySQLdb.connect ( host = hostaddr, user = username, passwd = password, db = database, cursorclass=MySQLdb.cursors.DictCursor )
#		conn.row_factory = sqlite3.Row
		cursor = conn.cursor()				#connect to users db	
		print "connection cursor created"
		cursor.execute ("SELECT * from DoorLog inner join members ON members.FobNumber=DoorLog.CardNum where DoorNum = '"+str(doorNum)+"' order by EventDateTime DESC")
		row = cursor.fetchone()			#get the record for the current cardnum
		print row
		return row

#	except Exception:
#		return ' database query failure!'


#getLastUnlock( 0 )

#irc event handlers
# Private messages
def handlePrivMessage ( connection, event ):
	
	print event.source().split ( '!' ) [ 0 ] + ': ' + event.arguments() [ 0 ]
	#check for doors argument
	door_name = event.arguments()[0].lower().lstrip().rstrip()
	print '..'+door_name+'..'
	#check for a valid door name	
	try:
		doorNum = doorNames.index(door_name,0)
		print door_name+' number is ',doorNum,'\n'
		#now retrieve from db the last event with that door number
		record = getLastUnlock( doorNum )
		connection.privmsg( event.source().split ( '!' ) [ 0 ],'Hey '
		+event.source().split ( '!' ) [ 0 ] + ', the '+ event.arguments() [ 0 ]
		+' door was last opened on '+record['EventDateTime']+' by '+record['FirstName'] )

	except ValueError:
		print door_name+' not a valid door name'
		#check for wildcard
		if( door_name == 'any' ):
			#check for each door
			for doorNum in range(len(doorNames)):
				record = getLastUnlock( doorNum )
				connection.privmsg( event.source().split ( '!' ) [ 0 ],'Hey '
				+event.source().split ( '!' ) [ 0 ] + ', the '+ doorNames[doorNum]
				+' door was last opened on '+record['EventDateTime']+' by '+record['FirstName'] )
		else:
			connection.privmsg( event.source().split ( '!' ) [ 0 ],'Sorry '
			+event.source().split ( '!' ) [ 0 ] + ', but '+ event.arguments() [ 0 ]
			+' is not a valid door name. Try "any" to get results on all valid doors')
	


def ircBOT():
	irclib.DEBUG = True	#to enable debug
	irc = irclib.IRC()
	#register private message handler
	irc.add_global_handler ( 'privmsg', handlePrivMessage )
	ircServer = irc.server()
	result = ircServer.connect(HOST, 6667, NICK, REALNAME)
	print result
	ircServer.join( CHAN )
	#ircServer.privmsg( CHAN, "Hello Again" )
	while 1:
		try:
			if not ircQueue.empty():
				ircMessage = ircQueue.get()
				print "ircBot thread received message "+ircMessage
				ircServer.privmsg( CHAN,ircMessage )
				#check if we have to quit
				if ircMessage == EXIT_MSG:
					ircServer.die()
					print "killing ircBOT!"
					break					
				ircQueue.task_done()

			#process any waiting http messages				
			irc.process_once(0.1)

		except Error:
			print Error	


#announce thread handles posing to irc/twitter
def msgAnnounce( userRow ):
	#check if we have a member record to announce
	if( row == None ):										#no record

		try:
			msg_body = "invalid fob number attempting to open the "+doorNames[int(cmdArgs[argDoorNumIndex])]+" door"
		except ValueError:
			msg_body = "invalid fob number attempting to open door "+cmdArgs[argDoorNumIndex]
		
		#send the announce
#		ircQueue.put(msg_body)
		print( msg_body )
#		subprocess.Popen(['twitter set "'+time.asctime( time.localtime(time.time()) )+': '+msg_body+'"'],shell=True) 	
			
		
	else:															#we have a valid record, build to message
		#check if we have permission to anounce this persion
		print userRow.keys()
		#check if enough time has passed since their last announce
		
		if( userRow[AnnounceField] == 'YES' ):
			#we have permission, spam irc and twitter
			#start with irc
			if( userRow[IRCName] != None ):
				ircName = userRow[IRCName]
			elif( userRow[NickName] != None ):
				ircName = userRow[NickName]
			else:
				ircName = userRow['FirstName']

			#next is twitter
			if( userRow[TwitName] != None ):
				twitName = userRow[TwitName]
			elif userRow[NickName] != None:
				twitName = userRow[NickName]
			else:
				twitName = userRow['FirstName']

			#next build the message
			try:
				msg_body = " entered the "+doorNames[int(cmdArgs[argDoorNumIndex])]
			except ValueError:
				msg_body = " attempted to unlocked door "+cmdArgs[argDoorNumIndex]		
			#send the announce
			print( ircName+msg_body )
			print( twitName+msg_body )
			ircQueue.put(ircName+msg_body)
			subprocess.Popen(['twitter set "'+time.asctime( time.localtime(time.time()) )+': '+twitName+msg_body+'"'],shell=True) 	
			

#start the ircBot thread
threading.Thread(target = ircBOT).start()

FAIL_EMAIL_TEXT="Mastercontrol has lost USB connection with the RFID board!! \nPlease ssh into 209.205.80.186 username doors, password Yoush4lln0tp4ss. \nConnect to screen session with screen -dr\nkill mastercontrol with ^C\nls/dev to see if there is a valid ttyUSB device. Edit RFIDServerMySQL.py, make sure it is using the correct port.\nRestart mastercontrol with: python RFIDServerMySQL.py\nGood Luck!" 	

try:
	ircQueue.put("I'm baaack!")
	while 1>0:
#		while 1:
#			time.sleep(1)	
		timeStart = time.time()
		cmd = ser.readline()							#read in a commadn string
		cmdTimeout = time.time() - timeStart	#calculate the time spent reading the serial port
		#check for failed comms
		if( cmd == '' and cmdTimeout < 4 ):		#check for serial failure without timeout
			#seem to have a failed serial connection
			ircQueue.put("Mastercontrol RFID communications down!!")
			print "Failed Serial Connection!!!"
			failMail("Mastercontrol lost usb connection!!", FAIL_EMAIL_TEXT)
			while 1:
				print "connection failure"			#output to screen and irc the failed state
				time.sleep(1200)
				ircQueue.put("Mastercontrol RFID communications down, RFID locks inoperative!!")


		print cmd
		cmd = cmd.strip('\n')
		cmd = cmd.strip('\r')
		cmdArgs = cmd.split(',')				#parse the arguments

		print cmdArgs
		if( cmdArgs[argCmdIndex] == "R" ):			#check for read argument
			#ircQueue.put("TEST"))
			conn = MySQLdb.connect ( host = hostaddr, user = username, passwd = password, db = database, cursorclass=MySQLdb.cursors.DictCursor )
	#		conn.row_factory = sqlite3.Row
			cursor = conn.cursor()				#connect to users db	
#			conn = sqlite3.connect(database)
#			conn.row_factory = sqlite3.Row
#			cursor = conn.cursor ()				#connect to users db
			cursor.execute ("SELECT * from members where "+FobField+" = '" + cmdArgs[argFobNumIndex]+"'")

			try:
				row = cursor.fetchone()			#get the record for the current cardnum
				print row
				if( row == None ):
					#no match for that user cardNum,
					#ser.write("  F,"+cmdArgs[argFobNumIndex]+","+cmdArgs[argDrinkNumIndex]+"  ")
					print " cardNumber "+ cmdArgs[argFobNumIndex]+" not found\n"

					#write to log
					log = open(logFile, 'a')		#open file to append to 
					log.write(time.asctime( time.localtime(time.time()) )+": ")
					log.write(cmdArgs[argFobNumIndex]+" is not in the database\n") 

					#write to db
					cursor.execute("insert into DoorLog values ("+cmdArgs[argFobNumIndex]+
							",'"+cmdArgs[argFobNumIndex]+" Not in database: "+cmd+
							"','"+cmdArgs[argDoorNumIndex]+"',now(),NULL)")
#							"','"+cmdArgs[argDoorNumIndex]+"',datetime('now','localtime'),NULL)")
					#send announce of failure
					msgAnnounce( row )
					conn.commit()
			
				else:
				
					ser.write(" A,"+cmdArgs[1]+",555 ")	#send admit command
					print cmdArgs[argFobNumIndex]+" unlocked door "+cmdArgs[argDoorNumIndex]+"\n"

					#write to log
					log = open(logFile, 'a')		#open file to append to 
					log.write(time.asctime( time.localtime(time.time()) )+": ")
					log.write(cmdArgs[argFobNumIndex]+" unlocked door "+cmdArgs[argDoorNumIndex]+"\n")

					#write to db
					cursor.execute("insert into DoorLog values ("+cmdArgs[argFobNumIndex]+
#							",'"+cmdArgs[argFobNumIndex]+" unlocked door "+cmdArgs[argDoorNumIndex]+
							",'"+row[FirstName]+" "+row[LastName]+" unlocked door "+cmdArgs[argDoorNumIndex]+
							"','"+cmdArgs[argDoorNumIndex]+"',now(),NULL)")
#							"','"+cmdArgs[argDoorNumIndex]+"',datetime('now','localtime'),NULL)")

					#check if we should send announce
					cursor.execute("select timestampdiff(second,'"+row[LastUnlockField]+"',now()) as seconds")
#					seconds = time.strftime('%s',time.asctime( time.localtime(time.time()) ))
#					seconds = seconds - time.strftime('%s',row[LastUnlockField])
#					cursor.execute("select strftime('%s','now','localtime') - strftime('%s','"+row[LastUnlockField]+"')")
#					print cursor
					seconds = cursor.fetchone()['seconds']
					print seconds
					if( int(seconds) > announceTimeoutSecs ):
						#send announce
						msgAnnounce( row )
					#update the db record to show that the member was announced at this time
					cursor.execute("update members set "+LastUnlockField+" =  now() where "+FobField+" = '"+cmdArgs[argFobNumIndex]+"'")					
#					cursor.execute("update members set "+LastUnlockField+" =  datetime('now','localtime') where "+FobField+" = '"+cmdArgs[argFobNumIndex]+"'")					
					conn.commit()
	

			except ValueError:		#Exception:
				#no match for that user cardNum, send 
			#server.serve_forever()back fail message
				print "Major Failure \n"
				#print Exception 

			#close the mysql connection		
			cursor.close ()
			conn.close ()
			#close log file
			log.close()

except KeyboardInterrupt:
	print '^C received, shutting down serverB'
	ircQueue.put("EXIT")


